Untangling the Web of PostgreSQL Permissions

PGConf EU
2024-10-23

Image by Claudia from Pixabay
logo EDB

Who am I

Image by Anemone123 from Pixabay

Agenda

  • Definitions
  • Attributes versus Permissions
  • Inheritance
  • Row level security
  • Best Practices

User

  • Needed to connect to a database
  • Unrelated to the OS user
  • Event though you might want some mapping
  • (See the pg_ident file for more details)
Image by Gerd Altmann from Pixabay

User

create user Nicole-Reine [options];
alter user Nicole-Reine [options];
drop user Nicole-Reine [options];
Image by Gerd Altmann from Pixabay

Group

  • Set of permissions
  • Can be assigned to a User
  • Makes administrating users easier

Group

create group West_Area_Computers [options];
alter group West_Area_Computers [options];
drop group West_Area_Computers [options];

Role

  • Generic term for a user or a group
  • A user is a role with login permissions
  • A group is a role
  • Literally, groups and users are aliases for roles
CREATE USER is now an alias for CREATE ROLE.
PostgreSQL Documentation
CREATE GROUP is now an alias for CREATE ROLE.
PostgreSQL Documentation
/*
 * CREATE ROLE
 */
Oid
CreateRole(
  ParseState *pstate,
  CreateRoleStmt *stmt
)
{
  [...]
  /* The defaults can vary depending on the original
   * statement type */
  switch (stmt->stmt_type)
  {
    case ROLESTMT_ROLE:
      break;
    case ROLESTMT_USER:
      canlogin = true;
      /* may eventually want inherit to default to
       * false here */
      break;
    case ROLESTMT_GROUP:
      break;
  }
  [...]
}

Role

create role Nicole-Reine [options];
alter role Nicole-Reine [options];
drop role Nicole-Reine [options];
create role West_Area_Computers [options];
alter role West_Area_Computers [options];
drop role West_Area_Computers [options];
Image by Melk Hagelslag from Pixabay
Image by Melk Hagelslag from Pixabay
Image by Melk Hagelslag from Pixabay
Image by Melk Hagelslag from Pixabay
Image by Melk Hagelslag from Pixabay
Image by Melk Hagelslag from Pixabay
Image by Melk Hagelslag from Pixabay
Image by Melk Hagelslag from Pixabay

Why?

In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.
PostgreSQL Documentation
Image by photosforyou from Pixabay

Why?

  • SQL-standard compliance
  • Groups and Users are different in Postgres 8.1
  • Patch idea: remove create|alter|drop user|group
Image by photosforyou from Pixabay

Role attributes

  • Not permissions
  • but priviledges!

Role attributes

CREATE | ALTER ROLE rolename WITH
  LOGIN | NOLOGIN
  SUPERUSER | NOSUPERUSER
  CREATEDB | NOCREATEDB
  CREATEROLE | NOCREATEROLE
  REPLICATION | NOREPLICATION
  PASSWORD 'passwd' | NULL
  INHERIT | NOINHERIT
  BYPASSRLS | NOBYPASSRLS
  CONNECTION LIMIT number

Role permissions

  • A table owner has all permissions on it
Image by Dmitriy from Pixabay
laetitia=> create role Beatrice_Worsley with login;
CREATE ROLE
laetitia=> create table test(
  id integer generated always as identity,
  value text);
CREATE TABLE
laetitia=> alter table test owner to beatrice_worsley ;
ALTER TABLE
Image by Dmitriy from Pixabay
laetitia=> set role beatrice_worsley ;
SET
laetitia=> insert into test(value)
  select 'Serial Programming for Real and Idealized Digital Calculating Machines';
INSERT 0 1
laetitia=> table test \gx
-[ RECORD 1 ]-----------------------------------------------------------------
id    | 1
value | Serial Programming for Real and Idealized Digital Calculating Machines

laetitia=> delete from test;
DELETE 1 
Image by Dmitriy from Pixabay

The public schema

laetitia=> create role Klára_Dán_von_Neumann with login;
CREATE ROLE
laetitia=> set role Klára_Dán_von_Neumann;
SET

laetitia=> create table test(
  id integer generated always as identity,
  value text);
ERROR:  permission denied for schema public
LINE 1: create table test(id integer generated always as identity, v...
                     ^
Image by Bayley Nargang from Pixabay
laetitia=> grant create on schema public to Klára_Dán_von_Neumann;
GRANT
laetitia=> set role Klára_Dán_von_Neumann;
SET
laetitia=> create table test(
  id integer generated always as identity,
  value text);
CREATE TABLE
Image by Bayley Nargang from Pixabay

Permissions

  • Depends on the kind of object
  • Are very precise
  • Permission handling can become very complex
  • Image by Dmitriy from Pixabay

Permissions for a table

  • create
  • select | insert | update | delete | truncate
  • references
  • truncate
  • all references
  • Image by Dmitriy from Pixabay
laetitia=> create table test(
  id integer generated always as identity,
  value text);
CREATE TABLE
laetitia=> alter table test owner to beatrice_worsley ;
ALTER TABLE
laetitia=> set role beatrice_worsley ;
SET
laetitia=> grant insert on table test to klára_dán_von_neumann;
GRANT
Image by Dmitriy from Pixabay
laetitia=> set role klára_dán_von_neumann;
SET
laetitia=> insert into test(value)
  values('The Computer and the Brain');
INSERT 0 1
laetitia=> select * from test;
ERROR:  permission denied for table test
Image by Dmitriy from Pixabay
laetitia=> create table test(id integer, value text);
CREATE TABLE
laetitia=> create sequence test_seq;
CREATE SEQUENCE
laetitia=> alter table test owner to beatrice_worsley ;
ALTER TABLE
laetitia=> set role beatrice_worsley ;
SET
laetitia=> insert into test(id, value)
  select nextval('test_seq'),
    'Serial Programming for Real and Idealized Digital Calculating Machines';
ERROR:  permission denied for sequence test_seq
Image by Dmitriy from Pixabay

Inheritance

  • To simplify permission managements
  • Create roles for different types of users
  • Assign them to the users
Image by svklimkin from Pixabay
laetitia=> create schema Joyce_Currie_Little;
CREATE SCHEMA
laetitia=> create role readonly;
CREATE ROLE
laetitia=> grant usage
  on schema Joyce_Currie_Little
  to readonly;
GRANT
laetitia=> grant select
  on all tables in schema Joyce_Currie_Little
  to readonly;
GRANT 
Image by svklimkin from Pixabay
laetitia=> alter default privileges
  in schema Joyce_Currie_Little
  grant select on tables to readonly;
ALTER DEFAULT PRIVILEGES
laetitia=> create table Joyce_Currie_Little.test(
  id integer,
  value text) ;
CREATE TABLE
Image by svklimkin from Pixabay
laetitia=> create role Gwen_Bell with login;
CREATE ROLE
laetitia=> grant readonly to Gwen_Bell ;
GRANT ROLE
laetitia=> set role gwen_bell ;
SET
laetitia=> select * from Joyce_Currie_Little.test;
 id | value
----+-------
(0 rows)
Image by svklimkin from Pixabay
laetitia=> set role laetitia;
SET
laetitia=> revoke readonly from gwen_bell ;
REVOKE ROLE
laetitia=> set role gwen_bell ;
SET
laetitia=> select * from Joyce_Currie_Little.test;
ERROR:  permission denied for schema joyce_currie_little
LINE 1: select * from Joyce_Currie_Little.test;
                      ^ 
Image by svklimkin from Pixabay
laetitia=> set role laetitia;
SET
laetitia=> grant usage
  on schema Joyce_Currie_Little
  to gwen_bell ;
GRANT
laetitia=> set role gwen_bell ;
SET
laetitia=> select * from Joyce_Currie_Little.test;
ERROR:  permission denied for table test 
Image by svklimkin from Pixabay
laetitia=> create role Cathy_Marshall with login;
CREATE ROLE
laetitia=> create role Sarah_Allen with login;
CREATE ROLE
laetitia=> alter default privileges in schema public
  grant select on tables to sarah_allen ;
ALTER DEFAULT PRIVILEGES
laetitia=> alter schema public owner to cathy_marshall ;
ALTER SCHEMA
Image by Gaertringen from Pixabay
laetitia=> set role cathy_marshall ;
SET
laetitia=> create table test (id integer, value text);
CREATE TABLE
laetitia=> set role sarah_allen ;
SET
laetitia=> table test;
ERROR:  permission denied for table test
laetitia=> \ddp
                    Default access privileges
  Owner   |    Schema     | Type  |   Access privileges    
----------+---------------+-------+------------------------
 laetitia | public        | table | sarah_allen=r/laetitia
(1 row)
Image by Gaertringen from Pixabay
laetitia=> set role laetitia;
SET
laetitia=> drop table test;
DROP TABLE
laetitia=> alter default privileges
  for role cathy_marshall in schema public
  grant select on tables to sarah_allen ;
ALTER DEFAULT PRIVILEGES
laetitia=> create table test (id integer, value text);
CREATE TABLE 
Image by Gaertringen from Pixabay
laetitia=> set role sarah_allen ;
SET
laetitia=> table test;
 id | value 
----+-------
(0 rows) 
Image by Gaertringen from Pixabay
laetitia=> \ddp
                          Default access privileges
     Owner      | Schema | Type  |      Access privileges       
----------------+---------------+-------+------------------------------
 cathy_marshall | public | table | sarah_allen=r/cathy_marshall
 laetitia       | public | table | sarah_allen=r/laetitia
(2 rows)
Image by Gaertringen from Pixabay

Why?

While you can change your own default privileges and the defaults of roles that you are a member of, at object creation time, new object permissions are only affected by the default privileges of the current role, and are not inherited from any roles in which the current role is a member.
PostgreSQL Documentation
Image by Gaertringen from Pixabay

Predefined roles

  • Designed to help
  • pg_monitor | pg_maintain | pg_create_subscription
  • pg_read_all_data | pg_write_all_data
Image by Gerd Altmann from Pixabay

Row Level Security (RLS)

  • Role-based
  • Restrict select | insert | update | delete rights
  • On certain rows (depending on a condition)
laetitia=> create table systers
  (admin text, member text, email_address text);
CREATE TABLE
laetitia=> create role member;
CREATE ROLE
laetitia=> create role admin;
CREATE ROLE
laetitia=> create role anita_borg with login;
CREATE ROLE
laetitia=> create role robin_jeffries with login;
CREATE ROLE
laetitia=> grant select on systers to admin, member;
GRANT
laetitia=> grant member to robin_jeffries;
GRANT ROLE
laetitia=> grant admin to anita_borg;
GRANT ROLE
laetitia=> alter table systers enable row level security;
ALTER TABLE
laetitia=> create policy systers_admin on systers
  to admin using (admin = current_user);
CREATE POLICY
laetitia=> create policy systers_member on systers
  to member using (member = current_user);
CREATE POLICY
laetitia=> insert into systers values
  ('anita_borg', 'robin_jeffries', 'robin.jeffries@mexample.com');
INSERT 0 1
laetitia=> insert into systers values
  ('anita_borg', 'rosario_robinson', 'rosario.robinson@example.com');
INSERT 0 1
laetitia=> set role anita_borg;
SET
laetitia=> select * from systers;
   admin    |      member      |        email_address
------------+------------------+------------------------------
 anita_borg | robin_jeffries   | robin.jeffries@mexample.com
 anita_borg | rosario_robinson | rosario.robinson@example.com
(2 rows)
laetitia=> set role robin_jeffries;
SET
laetitia=> select * from systers;
   admin    |     member     |        email_address
------------+----------------+-----------------------------
 anita_borg | robin_jeffries | robin.jeffries@mexample.com
(1 row) 

Best practices

  • Restrict permission to the maximum
  • Seriously, that's all
Image by StockSnap from Pixabay

Best practices

  • One user for monitoring
  • One user for backups
  • One user for each replication
  • One user for maintenance
  • One user RO for each application
  • One user RW for each application
  • Grant other permissions as needed
Image by StockSnap from Pixabay

To sum up

  • Permission management can become very complex
  • Try to see the bigger picture
  • Try to avoid exceptions
  • Try to grant only what's needed
Image by Felix Wolf from Pixabay

Question Time!